create or replace procedure question1 as 
begin
 update KHOA
 set SLSV = (Select count(*) from SINHVIEN
 where SINHVIEN.MAKH = KHOA.MAKH );
end question1;
create or replace procedure question2 as 
begin
  update SINHVIEN
 set DIEMTB = (Select avg(DIEM) from KETQUA
 where SINHVIEN.MASV = KETQUA.MASV 
 and LANTHI = (Select max(LANTHI) FROM KETQUA));
end question2;
create or replace procedure question3(nhapvao varchar2) as 
begin 
BEGIN
    BEGIN
      FOR item in
        (SELECT MASV, HoSV, TenSV, NGSINH
        from SINHVIEN
        where MAKH = nhapvao)
        LOOP
          dbms_output.put_line('Mã số SV: ' || item.MaSV || ' Họ: ' || item.HoSV || ' Tên SV: ' || item.TenSV
          || ' Ngày sinh: ' || item.NGSINH);
        END LOOP;
      END;
    END;
end question3;
create or replace procedure question4 as 
begin
 DECLARE
    pass number;
    fail number;
    BEGIN
      SELECT COUNT(MASV) INTO pass FROM KETQUA
      WHERE MAMH = ma AND DIEM >= 5;
      SELECT COUNT(MASV) INTO fail FROM KETQUA
      WHERE MAMH = ma AND DIEM >= 5;
      SELECT COUNT(MASV) INTO fail FROM KETQUA
      WHERE MAMH = ma AND KETQUA.DIEM < 5;
      
      dbms_output.put_line('Ma Mon hoc ' || ma);
      dbms_output.put_line('SL sv pass: ' || pass);
      dbms_output.put_line('SL sv fail: ' || fail);
end question4;